﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WAD.Model;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

namespace WAD.DAL
{
    /// <summary>
    /// 套餐数据库操作类
    /// </summary>
    public class DaoUserVersion : DAO
    {
        /// <summary>
        /// 添加套餐
        /// </summary>
        /// <param name="m"></param>
        /// <returns></returns>
        public static bool Add(MUserVersion m)
        {
            String sql = "insert into user_version([user_id],version_id,end_date)values(@user_id,@version_id,@end_date)";
            Database db = GetDatabase();
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@user_id", DbType.Int32, m.user_id);
            db.AddInParameter(cmd, "@version_id", DbType.Int32, m.version_id);
            db.AddInParameter(cmd, "@end_date", DbType.DateTime, m.end_date);
            return db.ExecuteNonQuery(cmd) > 0;
        }

        /// <summary>
        /// 获取一个实体对象
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static MUserVersion GetEntity(int id)
        {
            String sql = "select [user_id],version_id,[date],end_date,[state] from user_version where id = " + id;
            object obj = BindEntity(sql, new MUserVersion());
            return obj == null ? null : (MUserVersion)obj;
        }

        /// <summary>
        /// 查看是否存在该套餐
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static MUserVersion GetEntity(int user_id, int versionid)
        {
            String sql = "select [user_id],version_id,[date],end_date,[state] from user_version where version_id=" + versionid + " and [user_id] = " + user_id;
            object obj = BindEntity(sql, new MUserVersion());
            return obj == null ? null : (MUserVersion)obj;
        }



        /// <summary>
        /// 获取一个实体对象到Map
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static Dictionary<string, object> GetDetail(int id)
        {
            String sql = "select [user_id],version_id,[date],end_date,[state] from user_version where id = " + id;
            return BindMap(sql);
        }

        public static Dictionary<string, object> GetByUserId(int user_id)
        {
            String sql = "select [user_id],version_id,[date],end_date,[state] from user_version where state=1 and user_id = " + user_id + " order by id desc";
            return BindMap(sql);
        }


        /// <summary>
        /// 取消一个套餐
        /// </summary>
        /// <param name="id">套餐id</param>
        /// <param name="userId">用户id</param>
        /// <returns></returns>
        public static bool Hide(int id, int userId)
        {
            String sql = "update user_version set [state] = 0 where id = " + id + " and user_id = " + userId;
            return ExecuteNonQuery(sql) > 0;
        }

        /// <summary>
        /// 套餐续费
        /// </summary>
        /// <param name="m"></param>
        /// <returns></returns>
        public static bool Recharge(MUserVersion m)
        {
            String sql = "update user_version set end_date = @end_date where id = @id";
            Database db = GetDatabase();
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@end_date", DbType.DateTime, m.end_date);
            db.AddInParameter(cmd, "@id", DbType.Int32, m.id);
            return db.ExecuteNonQuery(cmd) > 0;
        }

        /// <summary>
        /// 更改套餐
        /// </summary>
        /// <param name="m"></param>
        /// <returns></returns>
        public static bool Updatecharge(MUserVersion m)
        {
            String sql = "update user_version set version_id = @version_id where version_id = @version_id and user_id=@user_id";
            Database db = GetDatabase();
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@version_id", DbType.Int32, m.version_id);
            db.AddInParameter(cmd, "@user_id", DbType.Int32, m.user_id);
            return db.ExecuteNonQuery(cmd) > 0;
        }


        /// <summary>
        /// 获取分页数据
        /// </summary>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="pageSize">每页显示的总条数</param>
        /// <returns></returns>
        public static PageData GetList(int pageIndex, int pageSize, int userId)
        {
            String fieldList = "[user_id],version_id,[date],end_date,[state]";
            String orderBy = "id desc";
            String where = "user_id = " + userId;
            String tableName = "user_version";
            return GetPageList(fieldList, orderBy, pageIndex, pageSize, where, tableName);
        }

        /// <summary>
        /// 获取套餐分页数据
        /// </summary>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="pageSize">每页显示的总条数</param>
        /// <returns></returns>
        public static PageData GetpaList(int pageIndex, int pageSize, int userId)
        {
            String fieldList = "id,[user_id],(select name from version as v where v.id= version_id) as version_name,CONVERT(nvarchar(20),[date],120) as [date],CONVERT(nvarchar(20),end_date,120) as end_date,[state]";
            String orderBy = "id desc";
            String where = "[state] = 1 and user_id = " + userId;
            String tableName = "user_version";
            return GetPageList(fieldList, orderBy, pageIndex, pageSize, where, tableName);
        }



        public static List<Dictionary<string, object>> GetList(int user_id, string feilds = "*")
        {
            return DAO.GetList(string.Format("select {0} from user_version where state=1 and user_id={1}", feilds, user_id));
        }

        //public static string GetVersion(int userid)
        //{
        //    string sql = string.Format("select  top 1 name from version where id in (select version_id from user_version where user_id={0}) order by id desc", userid);
        //    Database db = GetDatabase();
        //    DbCommand cmd = db.GetSqlStringCommand(sql);
        //    object o = db.ExecuteScalar(cmd);
        //    if (o == null)
        //        return "免费会员";
        //    return o.ToString();
        //}

        public static string GetVersion(int userid)
        {
            string sql = string.Format("select top 1 version_id from user_version where state=1 and user_id={0} order by id desc", userid);
            Database db = GetDatabase();
            DbCommand cmd = db.GetSqlStringCommand(sql);
            object o = db.ExecuteScalar(cmd);
            if (o == null)
                return "免费会员";
            return "正式版";
        }
    }
}
